import SourceGrid
import System
import System.Data.SQLite as sqlite

import clr
clr.AddReference('UnManagedCode')

from UnManagedCode import *
import Program

currentTask = ''
currentInfo = ''
currentStartTime = ''
currentEndTime = ''

def get_or_create_db():
    dbconn = sqlite.SQLiteConnection("Data Source=mysecs.db")  
    if System.IO.File.Exists('mysecs.db'):
            dbconn.Open()
    else:
            dbconn.Open()
            cmd = dbconn.CreateCommand()
            cmd. CommandText = """
                                    CREATE TABLE 'time_details'
                                    ('timeid' INTEGER PRIMARY KEY AUTOINCREMENT,
                                    'fromtime' VARCHAR,                                   
                                    'totime' VARCHAR ,
                                    'timespent' INTEGER,
                                    'appname' VARCHAR ,
                                    'apptitle' VARCHAR,
                                    'ipaddress' VARCHAR,
                                    'username' VARCHAR ,
                                    'domainname' VARCHAR,
                                    'machinename' VARCHAR,
                                    'osversion' VARCHAR,
                                    'category'  VARCHAR,
                                    'uploaded'  CHAR);
                                    """		
            cmd.ExecuteNonQuery()                
    return dbconn

def SetupGrid(form):    
    form._DataGrid.Redim(0,0)
    form._DataGrid.ColumnsCount = 6
    
    form._DataGrid.Rows.Insert(0)
    form._DataGrid[0,0] = SourceGrid.Cells.ColumnHeader("Start")
    form._DataGrid[0,1] = SourceGrid.Cells.ColumnHeader("End")
    form._DataGrid[0,2] = SourceGrid.Cells.ColumnHeader("SecsSpent")
    form._DataGrid[0,3] = SourceGrid.Cells.ColumnHeader("App/Task")
    form._DataGrid[0,4] = SourceGrid.Cells.ColumnHeader("Info")
    form._DataGrid[0,5] = SourceGrid.Cells.ColumnHeader("Category")
    
def DownloadToCSV(form):
    csv_path = "csvFile.csv"
    writer = System.IO.StreamWriter(csv_path, False, System.Text.Encoding.Default)
    csv = SourceGrid.Exporter.CSV()
    csv.Export(form._DataGrid, writer)
    writer.Close()
   

def RefreshGrid(form):
    dbconn=get_or_create_db()
    dbcmd = dbconn.CreateCommand()    
    dbcmd.CommandText = 'select fromtime,totime,timespent,appname,apptitle,category, ipaddress,domainname,osversion from time_details order by fromtime desc'
    SetupGrid(form)
    timedetails = dbcmd.ExecuteReader()
    TotalRows = form._DataGrid.RowsCount
    while timedetails.Read():
        form._DataGrid.Rows.Insert(TotalRows)
        form._DataGrid[TotalRows,0] = SourceGrid.Cells.Cell(timedetails[0])
        form._DataGrid[TotalRows,1] = SourceGrid.Cells.Cell(timedetails[1])
        form._DataGrid[TotalRows,2] = SourceGrid.Cells.Cell(timedetails[2])
        form._DataGrid[TotalRows,3] = SourceGrid.Cells.Cell(timedetails[3])
        form._DataGrid[TotalRows,4] = SourceGrid.Cells.Cell(timedetails[4])
        form._DataGrid[TotalRows,5] = SourceGrid.Cells.Cell(timedetails[5])
        TotalRows += 1
    dbconn.Close()
    form._DataGrid.AutoSizeCells()

def GetWindowDetails():
    hd = User32.GetForegroundWindow()
    WndTitleChars = 256
    AppTitle = System.Text.StringBuilder(WndTitleChars)
    pid = 0
    AppName = ""
    if User32.GetWindowText(hd, AppTitle , WndTitleChars):    
        pid = clr.Reference[System.Int32](0)
        User32.GetWindowThreadProcessId(hd, pid)
        ActiveProcess = System.Diagnostics.Process.GetProcessById(pid.Value)
        AppName =  ActiveProcess.ProcessName
    return dict(AppTitle = str(AppTitle), AppName = AppName)

def GetGeneralDetails():
    UserDomainName = System.Environment.UserDomainName
    UserName = System.Environment.UserName
    MachineName = System.Environment.MachineName
    OSVersion = str(System.Environment.OSVersion)
    HostAddress = list(System.Net.Dns.Resolve(System.Net.Dns.GetHostName()).AddressList)[0]
    return dict(UserDomainName=UserDomainName, UserName=UserName, MachineName=MachineName, OSVersion=OSVersion, HostAddress=HostAddress)

def SnapshotDetails(sender, event):    
    GeneralDetails = GetGeneralDetails()
    WndDetails = GetWindowDetails()
    timeSpent = 0
    timeformat = "yyyy-MM-dd HH:mm:ss"
    
    global currentStartTime, currentTask, currentInfo, currentEndTime
    
    Program.mysecs10.mysecsLog.Log("timer tick at %s; app %s; app-info: %s" % (System.DateTime.Now, currentTask, currentInfo))
    
    if currentStartTime == '':
        #program is starting        
        currentStartTime = System.DateTime.Now
        Program.mysecs10.mysecsLog.Log("first call to snapshot details at: %s" % currentStartTime)
        currentTask = WndDetails['AppName']
        currentInfo = WndDetails['AppTitle']
    else:
        #user has gone to another apps
        #write into db
        #start a new cycle
        if (currentTask != WndDetails['AppName']) and (currentInfo != WndDetails['AppTitle']):
            currentEndTime = System.DateTime.Now
            timeSpent = currentEndTime.Subtract(currentStartTime)            
            timeSpentinSec = timeSpent.TotalSeconds.ToString()
            if Program.mysecs10.categories.has_key(currentTask.lower()):
                appCategory = Program.mysecs10.categories[currentTask.lower()]                 
            else:
                appCategory = 'General'
                        
            Program.mysecs10.mysecsLog.Log("spent %s secs on %s cat is:%s" % (timeSpentinSec, currentTask, appCategory))
            
            #should we track this app?
            if not currentTask.lower() in Program.mysecs10.donttracklist:            
                #write into db
                if currentTask == "":
                    currentTask = "Idle"
                    currentInfo = "Idle"
                    appCategory = "Idle"
                dbconn = get_or_create_db()
                dbcmd = dbconn.CreateCommand()
                dbcmd.CommandText = """insert into time_details 
                                        (FromTime,
                                        ToTime,
                                        TimeSpent,
                                        AppName,
                                        AppTitle,
                                        IPAddress,
                                        UserName,
                                        DomainName,
                                        MachineName,
                                        OSVersion,
                                        Category,
                                        Uploaded
                                        )
                                    values(
                                        @FromTime,
                                        @ToTime,
                                        @TimeSpent,
                                        @AppName,
                                        @AppTitle,
                                        @IPAddress,
                                        @UserName,
                                        @Domain,
                                        @MachineName,
                                        @OSVersion,
                                        @Category,
                                        @Uploaded
                                        )
                                    """
                dbcmd.Parameters.AddWithValue("@FromTime", currentStartTime.ToString(timeformat))
                dbcmd.Parameters.AddWithValue("@ToTime", currentEndTime.ToString(timeformat))
                dbcmd.Parameters.AddWithValue("@TimeSpent", timeSpentinSec)
                dbcmd.Parameters.AddWithValue("@AppName", currentTask)
                dbcmd.Parameters.AddWithValue("@AppTitle", currentInfo)
                dbcmd.Parameters.AddWithValue("@IPAddress",GeneralDetails['HostAddress'])
                dbcmd.Parameters.AddWithValue("@UserName",GeneralDetails['UserName'])
                dbcmd.Parameters.AddWithValue("@Domain",GeneralDetails['UserDomainName'])
                dbcmd.Parameters.AddWithValue("@MachineName",GeneralDetails['MachineName'])
                dbcmd.Parameters.AddWithValue("@OSVersion",GeneralDetails['OSVersion'])
                dbcmd.Parameters.AddWithValue("@Category",appCategory)
                dbcmd.Parameters.AddWithValue("@Uploaded",0)
                
                dbcmd.ExecuteNonQuery()
                dbconn.Close()
                
            currentTask = WndDetails['AppName']
            currentInfo = WndDetails['AppTitle']
            currentStartTime = currentEndTime